{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shazi,WorlWhat happened?dshazi,WorlWhat happened?dshazi,WorlWhat happened?dshazi,WorlWhat happened?dshazi,WorlWhat happened?dshazi,WHat happened?World傻子筛子筛子筛子筛子筛子筛子\n"
     ]
    }
   ],
   "source": [
    "from win32com.client import Dispatch\n",
    "word = Dispatch('Word.Application')  \n",
    "word.Visible = 0  \n",
    "word.DisplayAlerts = 0 \n",
    "path = r'C:\\Users\\Administrator\\Desktop\\python\\Homework\\homework related\\related.doc'\n",
    "doc = word.Documents.Open(path)\n",
    "for para in doc.paragraphs:\n",
    "    print(para)\n",
    "myRange1 =doc.Range(0,0)\n",
    "myRange1.InsertBefore('Hello,World')\n",
    "myRange2 =doc.Range()\n",
    "myRange2.InsertAfter('筛子')\n",
    "myRange3= doc.Range(0,10) \n",
    "myRange3.InsertAfter('What happened?')\n",
    "word.Selection.Find.Replacement.ClearFormatting()\n",
    "word.Selection.Find.Execute('hello', False, False, False, False, False, True,1, True, 'shazi', 2)\n",
    "while word.Selection.Find.Execute(str, False, False, False, False, False, True, 0, True, \"\", 0):\n",
    "\n",
    "    word.Selection.Range.HighlightColorIndex= 11\n",
    "\n",
    "    word.Selection.Font.Color= 255 \n",
    "doc.Close()\n",
    "word.Quit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Get data type: <class 'collections.OrderedDict'>\n",
      "sheet1 : [['姓名', '年龄'], ['彤彤', 18]]\n"
     ]
    }
   ],
   "source": [
    "#coding=utf-8\n",
    "\n",
    "from collections import OrderedDict\n",
    "from pyexcel_xls import get_data\n",
    "from pyexcel_xls import save_data\n",
    "path = r'C:\\Users\\Administrator\\Desktop\\python\\Homework\\homework related\\related.xls' \n",
    "xls_data = get_data(path)\n",
    "print (\"Get data type:\", type(xls_data))\n",
    "for sheet_n in xls_data.keys():\n",
    "    print (sheet_n, \":\", xls_data[sheet_n])\n",
    "\n",
    "data = OrderedDict()\n",
    "sheet_1 = []\n",
    "row_1_data = ['name','age','favorite']\n",
    "row_2_data = ['shazi', '40', 'banana']\n",
    "sheet_1.append(row_1_data)\n",
    "sheet_1.append(row_2_data)\n",
    "data.update({u\"这是我的表\": sheet_1})\n",
    "save_data(path, data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "name\n",
      "banana\n"
     ]
    }
   ],
   "source": [
    "###pywin32 API\n",
    "#coding = utf-8\n",
    "from win32com.client import Dispatch \n",
    "application = Dispatch('Excel.Application')\n",
    "application.Visible = 0  \n",
    "application.DisplayAlerts = 0\n",
    "path_excel = r'C:\\Users\\Administrator\\Desktop\\python\\Homework\\homework related\\related.xls' \n",
    "workbook = application.Workbooks.Open(path_excel)\n",
    "worksheet = workbook.Worksheets('My Sheet')\n",
    "print(worksheet.Cells(1,1))\n",
    "print(worksheet.Cells(2,3))\n",
    "worksheet.Cells(4,1).Value='hello'\n",
    "worksheet.Name = 'My Favorite Sheet'\n",
    "worksheet.SaveAs(r'C:\\Users\\Administrator\\Desktop\\python\\Homework\\homework related\\new.xls')\n",
    "workbook.Close()\n",
    "application.Quit()\n",
    "#doc操作看第一个，xlsx同理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "获取到所有的值:\n",
      "    name   age favorite   爱\n",
      "0  shazi  40.0   banana NaN\n",
      "1    NaN   NaN      NaN NaN\n",
      "2  hello   NaN      NaN NaN\n",
      "    name favorite   爱\n",
      "0  shazi   banana NaN\n",
      "1    NaN      NaN NaN\n",
      "2  hello      NaN NaN\n",
      "    name   age\n",
      "0  shazi  40.0\n",
      "1    NaN   NaN\n",
      "2  hello   NaN\n",
      "    name   age favorite   爱\n",
      "0  shazi  40.0   banana NaN\n",
      "1    NaN   NaN      NaN NaN\n",
      "2  hello   NaN      NaN NaN\n",
      "   name  age  favorite   爱\n",
      "0   NaN  NaN       NaN NaN\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Administrator\\AppData\\Local\\Temp\\ipykernel_3140\\2644405717.py:22: FutureWarning: As the xlwt package is no longer maintained, the xlwt engine will be removed in a future version of pandas. This is the only engine in pandas that supports writing in the xls format. Install openpyxl and write to an xlsx file instead. You can set the option io.excel.xls.writer to 'xlwt' to silence this warning. While this option is deprecated and will also raise a warning, it can be globally set and the warning suppressed.\n",
      "  data5.to_excel(\"new.xls\", sheet_name=\"Student\")\n",
      "C:\\Users\\Administrator\\AppData\\Local\\Temp\\ipykernel_3140\\2644405717.py:45: FutureWarning: As the xlwt package is no longer maintained, the xlwt engine will be removed in a future version of pandas. This is the only engine in pandas that supports writing in the xls format. Install openpyxl and write to an xlsx file instead. You can set the option io.excel.xls.writer to 'xlwt' to silence this warning. While this option is deprecated and will also raise a warning, it can be globally set and the warning suppressed.\n",
      "  with pa.ExcelWriter('stocks_weather.xls') as writer:\n"
     ]
    }
   ],
   "source": [
    "#code = utf-8\n",
    "#pandas库对Excel文档的处理\n",
    "import pandas as pa\n",
    "path = r'C:\\Users\\Administrator\\Desktop\\python\\Homework\\homework related\\related.xls' \n",
    "df = pa.read_excel(path,sheet_name = 'My Sheet')\n",
    "data1 = df.head()\n",
    "print('获取到所有的值:\\n{0}'.format(data1))\n",
    "data2 = pa.read_excel(path,usecols=\"A,C,D\")\n",
    "print(data2)\n",
    "data2 = pa.read_excel(path,usecols=['name','age'])\n",
    "print(data2)\n",
    "\n",
    "data3=pa.read_excel(path,nrows=4)\n",
    "print(data3)\n",
    "\n",
    "data4 = pa.read_excel(path,skiprows=[1,3])\n",
    "print(data4)\n",
    "\n",
    "\n",
    "data5 = pa.read_excel(path)\n",
    "\n",
    "data5.to_excel(\"new.xls\", sheet_name=\"Student\")\n",
    "\n",
    "\n",
    "df_stock = pa.DataFrame({\n",
    "\n",
    "'tickers':['GOOGLE','WMT','MSFT'],\n",
    "\n",
    "'price':[400,600,900],\n",
    "\n",
    "'eps':[20.80,6.90,90.8]\n",
    "\n",
    "})\n",
    "\n",
    "df_weather = pa.DataFrame({\n",
    "\n",
    "'day':['1/9/17','2/9/17','3/9/17'],\n",
    "\n",
    "'temperature':[40,60,30],\n",
    "\n",
    "'event':['Rainy','Sunny','Snow']\n",
    "\n",
    "})\n",
    "\n",
    "with pa.ExcelWriter('stocks_weather.xls') as writer:\n",
    "\n",
    "    df_stock.to_excel(writer,sheet_name=\"stocks\")\n",
    "\n",
    "    df_weather.to_excel(writer,\"weather\")\n"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "570feb405e2e27c949193ac68f46852414290d515b0ba6e5d90d076ed2284471"
  },
  "kernelspec": {
   "display_name": "Python 3.8.10 64-bit",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
